<?php
class m_relasi_donatur_tunggal extends Model{
    var $table = "relasi_donatur_tunggal",
        $table2 = "keuangan_donatur_tunggal",
        $table3 = "keuangan_adik_asuh";    
    function m_relasi_donatur_tunggal(){
        parent::Model();
        $this->load->model('m_adik_asuh');
    }
    function getRelasiDonaturTunggalAdikAsuh(){
        $qDonatur = $this->db->query("select jk.id_member,m.nama from jenis_keanggotaan jk 
        join member m on jk.id_member = m.id
        where jk.id_jenis_member = 3");
        $result = array();
        if($qDonatur->num_rows() > 0){
            $i = 0;
            foreach ($qDonatur->result_array() as $row){
                $result[$i]['donaturTunggal'] = $row;
                $qAdikAsuh = $this->db->query("select rd.*,a.nama as adik_asuh,s.nama as sekolah,kls.nama as kelas,tp.nama as tingkat_pendidikan,ta.nama as tahun_ajaran,sms.nama as semester from relasi_donatur_tunggal rd 
                        join adik_asuh a on rd.id_adik_asuh = a.id
                        join dinamis_adik_asuh da on da.id_adik_asuh = a.id
                        join sekolah s on da.id_sekolah = s.id
                        join kelas kls on da.id_kelas = kls.id
                        join tingkat_pendidikan tp on kls.id_tingkat_pendidikan = tp.id
                        join tahun_ajaran ta on da.id_tahun_ajaran = ta.id
                        join semester sms on ta.id_semester = sms.id
                        where rd.id_member = '$row[id_member]' and da.status = '1'");
                if($qAdikAsuh->num_rows() > 0){
                    foreach ($qAdikAsuh->result_array() as $rows){
                        $result[$i]['adik_asuh'][] = $rows;
                    }
                }else $result[$i]['adik_asuh'][] = array();
                $i++;
            }
        }
        return $result;
    }
    function getRelasiDonaturTunggal($idDonaturTunggal = NULL,$id = NULL){
        if($idDonaturTunggal != NULL){
            $where = "and rd.id_member = '$idDonaturTunggal'";
        }if($id != NULL){
            $where = "and rd.id = '$id'";
        }
        $query = $this->db->query("select m.nama as member,m.id as id_member,dm.alamat as alamat_donatur,dm.no_telp as telp_member,rd.id,a.nama as adik_asuh,a.tgl_lahir,a.jenis_kelamin,k.nama as kelas,a.id as id_adik_asuh,tk.nama as tingkat_pendidikan,ta.nama as tahun_ajaran,sm.nama as semester,da.alamat,s.nama as sekolah from relasi_donatur_tunggal rd 
                                  join member m on rd.id_member = m.id
                                  join dinamis_member dm on dm.id_member = m.id
                                  join adik_asuh a on rd.id_adik_asuh = a.id
                                  join dinamis_adik_asuh da on da.id_adik_asuh = a.id 
                                  join sekolah s on da.id_sekolah = s.id 
                                  join kelas k on da.id_kelas = k.id
                                  join tingkat_pendidikan tk on s.id_tingkat_pendidikan = tk.id
                                  join tahun_ajaran ta on da.id_tahun_ajaran = ta.id
                                  join semester sm on ta.id_semester = sm.id
                                  where da.status = '1' and dm.status = '1' $where");
        if($query->num_rows() > 0){
            $result =  $query->result_array();
        }else $result =  array();
        
        return $result;
    }
    function getSumDebet($idDonatur = NULL){
        if($idDonatur != ""){
            $where = "where id_donatur_tunggal = '$idDonatur'";
        }else $where = "";
        
        $query = $this->db->query("select SUM(debet) as debet_total from keuangan_donatur_tunggal $where");
        return $query->result_array();
    }
    function getSumKredit($idDonatur = NULL){
        if($idDonatur != ""){
            $where = "where id_donatur_tunggal = '$idDonatur'";
        }else $where = "";
        
        $query = $this->db->query("select SUM(kredit) as kredit_total from keuangan_donatur_tunggal $where");
        return $query->result_array();
    }
    function searchDonaturTunggal($nama){
        $query = $this->db->query("select jk.id_member,m.nama as member,dm.alamat,dm.no_telp from jenis_keanggotaan jk 
        join member m on jk.id_member = m.id
        join dinamis_member dm on dm.id_member = m.id
        where jk.id_jenis_member = 3 and dm.status = '1' and locate('$nama',m.nama) > 0 order by locate('$nama',m.nama)");
        if($query->num_rows() > 0){
            $result =  $query->result_array();
        }else $result =  array();
        
        return $result;
    }
    function searchRelasiDonaturTunggal($nama){
        $query = $this->db->query("select a.id as id_adik_asuh,a.nama as adik_asuh,da.alamat,s.nama as sekolah from adik_asuh a
                                  join dinamis_adik_asuh da on da.id_adik_asuh = a.id
                                  join sekolah s on da.id_sekolah = s.id
                                  where a.id not in (select id_adik_asuh from relasi_donatur_tunggal) and da.status = '1' and locate('$nama',a.nama) > 0 order by locate('$nama',a.nama)");
        if($query->num_rows() > 0){
            $result =  $query->result_array();
        }else $result =  array();
        
        return $result;
    }
    function searchDonasiDonaturTunggal($nama,$idMember){
        $query = $this->db->query("select a.id as id_adik_asuh,a.nama as adik_asuh,tp.nama as tingkat_pendidikan,tp.id as id_tingkat_pendidikan,da.alamat,s.nama as sekolah,s.id as id_sekolah,k.id as id_kelas,k.nama as kelas,ta.id as id_tahun_ajaran,ta.nama as tahun_ajaran,sms.nama as semester from relasi_donatur_tunggal rdt
                                  join adik_asuh a on rdt.id_adik_asuh = a.id
                                  join dinamis_adik_asuh da on da.id_adik_asuh = a.id
                                  join sekolah s on da.id_sekolah = s.id
                                  join kelas k on da.id_kelas = k.id
                                  join tingkat_pendidikan tp on k.id_tingkat_pendidikan = tp.id
                                  join tahun_ajaran ta on da.id_tahun_ajaran = ta.id
                                  join semester sms on ta.id_semester = sms.id
                                  where da.status = '1' and rdt.id_member = '$idMember' and locate('$nama',a.nama) > 0 order by locate('$nama',a.nama)");
        if($query->num_rows() > 0){
            $result =  $query->result_array();
        }else $result =  array();
        
        return $result;
    }
    function addRelasiDonaturTunggal($data){
        $insert = $this->db->insert($this->table,$data);
        if(!$insert){
            return FALSE;
        }else return TRUE;
    }
    function editRelasiDonaturTunggal($data){
        $this->db->where('id',$data['id']);
        $update = $this->db->update($this->table,$data);
        if(!$update){
            return FALSE;
        }else return TRUE;
    }
    function deleteRelasiDonaturTunggal($id){
        $delete = $this->db->delete($this->table,array('id'=>$id));
        if(!$delete){
            return FALSE;
        }else return TRUE;
    }
    function addKeuanganDonaturTunggal($data){
        foreach ($this->getSumDebet($data['id_donatur_tunggal']) as $sumDebet);
        foreach ($this->getSumKredit($data['id_donatur_tunggal']) as $sumKredit);
        $idPetugas = $this->session->userdata('id_member');
        
        if($data['jenis_transaksi'] == "debet"){
            $dataDebet = array('tanggal' => $data['tanggal'],
                'id_petugas' => $idPetugas,
                'id_donatur_tunggal' => $data['id_donatur_tunggal'],
                'debet' => $data['jumlah'],
                'saldo' => (($sumDebet['debet_total']-$sumKredit['kredit_total'])+$data['jumlah']),
                'keterangan' => $data['keterangan'],
                'status' => '0');
            $insert = $this->db->insert($this->table2,$dataDebet);
            $id = $this->db->insert_id();
            $return['status'] = TRUE;
            $return['id'] = $id;
        }else if($data['jenis_transaksi'] == "kredit"){
          $qcek = $this->db->query("select count(*) as cek from keuangan_adik_asuh where id_adik_asuh = '$data[id_adik_asuh]' and id_tahun_ajaran = '$data[id_tahun_ajaran]' and saldo = 180000");
          foreach ($qcek->result_array() as $cek);
          if($cek['cek'] > 0){
             $return['status'] = FALSE;
          }else{
            foreach ($this->m_adik_asuh->getTotalDebet($data['id_adik_asuh']) as $totalDebet);
            foreach ($this->m_adik_asuh->getTotalKredit($data['id_adik_asuh']) as $totalKredit);
            $dataKredit = array('tanggal' => $data['tanggal'],
                'id_petugas' => $idPetugas,
                'id_donatur_tunggal' => $data['id_donatur_tunggal'],
                'kredit' => $data['jumlah'],
                'saldo' => (($sumDebet['debet_total']-$sumKredit['kredit_total'])-$data['jumlah']),
                'keterangan' => $data['keterangan'],
                'status' => '0');
            $insert = $this->db->insert($this->table2,$dataKredit);
            $id = $this->db->insert_id();
            
            $donasiAdikAsuh = array('tanggal' => $data['tanggal'],
                'id_petugas' => $idPetugas,
                'id_adik_asuh' => $data['id_adik_asuh'],
                'id_sekolah' => $data['id_sekolah'],
                'id_kelas' => $data['id_kelas'],
                'id_tahun_ajaran' => $data['id_tahun_ajaran'],
                'debet' => $data['jumlah'],
                'saldo' => (($totalDebet['total_debet']-$totalKredit['total_kredit'])+$data['jumlah']),
                'keterangan' => $data['keterangan']
                );
            $insertDonasi = $this->db->insert($this->table3,$donasiAdikAsuh);
            $return['status'] = TRUE;
            $return['id'] = $id;
         }    
        }
        return $return;
    }
    function getListKeuanganDonaturTunggal($id){
        $query = $this->db->query("select kdt.tanggal,kdt.debet,kdt.kredit,kdt.keterangan,m1.nama as petugas,m2.nama as donatur_tunggal from keuangan_donatur_tunggal kdt
        join member m1 on kdt.id_petugas = m1.id
        join member m2 on kdt.id_donatur_tunggal = m2.id where kdt.id='$id'");
        
        if($query->num_rows() > 0){
            $result =  $query->result_array();
        }else $result =  array();
        
        return $result;
    }
}
?>
